package idv.arthur.work;

import java.io.IOException;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.google.gson.Gson;

public class QueryDep extends HttpServlet {
	private static final long serialVersionUID = 1L;
      
    public QueryDep() {
        super();
    }
    protected void doGet(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException {
    	doPost(request,response);
    }

	protected void doPost(HttpServletRequest request,HttpServletResponse response) throws ServletException,IOException {
		
		Widgets wid = new Widgets();
		request.setCharacterEncoding( wid.UIcodeSet );
		response.setContentType("application/json");
		response.setCharacterEncoding( wid.UIcodeSet );
		
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		ResultSet rs2 = null;
		ResultSet rs3 = null;
		String errPars = "}";
		ArrayList<QueryDepRes> qdres = new ArrayList<QueryDepRes>();
		
		final boolean isSU = Boolean.parseBoolean( request.getParameter("isSU") );
		final String currentUser = request.getParameter("userId");
		final String q_deliverySN = request.getParameter("q_deliverySN");
		final String q_deplmainSN = request.getParameter("q_deplmainSN");
		final String q_fileName = request.getParameter("q_fileName");
		final String q_performerId = request.getParameter("q_performerId");	
		final String q_squadid = request.getParameter("q_squadid");
		final String q_fromDate = request.getParameter("q_fromDate");
		final String q_toDate = request.getParameter("q_toDate");
		final String q_envSubSysId = request.getParameter("q_envSubSysId");
		final String q_envId = request.getParameter("q_envId");
		
		String subAccess = "";
		
		
		StringBuilder strSQL1 = new StringBuilder();	/*	用來確定查詢是有結果的	*/
		StringBuilder strSQL2 = new StringBuilder();	/*	用來抓取查詢結果	*/
		StringBuilder strSQL3 = new StringBuilder(); /*	查詢該筆部署至資源群組的數目	*/
		
		try {
			conn = wid.getConn(true);
			
			if ( !isSU ) {
				subAccess = " t6.subsysid IN(";
				strSQL1.replace(0, strSQL1.length(), "SELECT subsysid FROM mproleuser WHERE userid=? AND roleid=3" );
				ps = conn.prepareStatement( strSQL1.toString() );
				ps.setString(1, currentUser );
				rs = ps.executeQuery();
				while ( rs.next() ) {
					subAccess += "'"+rs.getString("subsysid")+"',";
				}
				subAccess = subAccess.substring(0, subAccess.length()-1);
				subAccess += ") AND ";	
			}
			
			
			strSQL1.replace( 0, strSQL1.length(), "SELECT COUNT(*) FROM depmain t1,depdetail t2,mpdeplunitsqu t3,personnel t4,depldetail t5,deplmain t6 WHERE t2.deplmainSN=t6.deplmainSN AND t5.deplmainSN=t2.deplmainSN AND t5.depldetailid=t3.depldetailid AND t1.depmainSN=t2.depmainSN AND t2.deplmainSN=t3.deplmainSN AND "+subAccess+" t2.depldetailid=t3.depldetailid AND t1.creator=t4.userID ");
			
			if ( conn.getMetaData().getDatabaseProductName().contains( "MySQL" ) ) {
				strSQL2.replace(0, strSQL2.length(), "SELECT t5.isnonweb,t7.deliveryTarSN,t6.deplComment,t5.depPath,t5.localPath,t10.subsysname,t9.squadname,t8.envname,t2.depmainSN,t2.depdetailid,t1.envid,t7.deliverySN,t5.localFile,t5.localVer,t3.bssid,t3.subsysid,t3.squadid,t1.deplmainSN,t4.username,t1.createdDateTime FROM (subsys t10,squad t9,env t8,depmain t1,depdetail t2,mpdeplunitsqu t3,personnel t4,depldetail t5,deplmain t6) LEFT JOIN deltask t7 ON t7.deplmainSN=t6.deplmainSN WHERE t2.deplmainSN=t6.deplmainSN AND t5.deplmainSN=t2.deplmainSN AND t5.depldetailid=t3.depldetailid AND t1.depmainSN=t2.depmainSN AND t2.deplmainSN=t3.deplmainSN AND t2.depldetailid=t3.depldetailid AND t1.envid=t8.envid AND t8.bssid=t3.bssid AND t9.bssid=t3.bssid AND t9.subsysid=t3.subsysid AND t10.bssid=t3.bssid AND t10.subsysid=t3.subsysid AND t9.subsysid=t3.subsysid AND t9.squadid=t3.squadid AND t3.subsysid=t8.subsysid AND t1.creator=t4.userID ");				
			} else if ( conn.getMetaData().getDatabaseProductName().contains( "Informix" ) ) {
				strSQL2.replace(0, strSQL2.length(), "SELECT t5.isnonweb,t7.deliveryTarSN,t6.deplComment,t5.depPath,t5.localPath,t10.subsysname,t9.squadname,t8.envname,t2.depmainSN,t2.depdetailid,t1.envid,t7.deliverySN,t5.localFile,t5.localVer,t3.bssid,t3.subsysid,t3.squadid,t1.deplmainSN,t4.username,t1.createdDateTime FROM subsys t10,squad t9,env t8,depmain t1,depdetail t2,mpdeplunitsqu t3,personnel t4,depldetail t5,deplmain t6, OUTER deltask t7 WHERE t7.deplmainSN=t6.deplmainSN AND t2.deplmainSN=t6.deplmainSN AND t5.deplmainSN=t2.deplmainSN AND t5.depldetailid=t3.depldetailid AND t1.depmainSN=t2.depmainSN AND t2.deplmainSN=t3.deplmainSN AND t2.depldetailid=t3.depldetailid AND t1.envid=t8.envid AND t8.bssid=t3.bssid AND "+subAccess+" t9.bssid=t3.bssid AND t9.subsysid=t3.subsysid AND t10.bssid=t3.bssid AND t10.subsysid=t3.subsysid AND t9.subsysid=t3.subsysid AND t9.squadid=t3.squadid AND t3.subsysid=t8.subsysid AND t1.creator=t4.userID ");
			}
			
			strSQL3.replace(0, strSQL3.length(), "SELECT t2.depmainSN,COUNT(t2.depdetailid) FROM depmain t1,depdetail t2,mpdeplunitsqu t3,personnel t4,depldetail t5,deplmain t6 WHERE t2.deplmainSN = t6.deplmainSN AND t5.deplmainSN=t2.deplmainSN AND t5.depldetailid=t3.depldetailid AND t1.depmainSN=t2.depmainSN AND "+subAccess+"  t2.deplmainSN=t3.deplmainSN AND t2.depldetailid=t3.depldetailid AND t1.creator=t4.userID ");	
			
			StringBuilder strSQL4 = new StringBuilder();
					
			
			
			if (!"".equalsIgnoreCase(q_envSubSysId)) {
				strSQL1.append( "AND t3.subsysid='"+q_envSubSysId+"' ");
				strSQL2.append("AND t10.subsysid='"+q_envSubSysId+"' ");
				strSQL3.append("AND t3.subsysid='"+q_envSubSysId+"' ");
			}
			if (!"".equalsIgnoreCase(q_envId)) {
				strSQL1.append( "AND t1.envid='"+q_envId+"' ");
				strSQL2.append("AND t8.envid='"+q_envId+"' ");
				strSQL3.append("AND t1.envid='"+q_envId+"' ");
			}
			if (!"".equalsIgnoreCase(q_deliverySN)) {
				strSQL1.append( "AND t7.deliverySN="+q_deliverySN+" ");
				strSQL2.append("AND t7.deliverySN="+q_deliverySN+" ");
				strSQL3.append("AND t7.deliverySN="+q_deliverySN+" ");
			}
			if (!"".equalsIgnoreCase(q_deplmainSN)) {
				strSQL1.append(" AND t6.deplmainSN="+q_deplmainSN+" ");
				strSQL2.append(" AND t2.deplmainSN="+q_deplmainSN+" ");
				strSQL3.append(" AND t2.deplmainSN="+q_deplmainSN+" ");
			}
			if (!"".equalsIgnoreCase(q_fileName)) {
				strSQL1.append( "AND t5.localFile='"+q_fileName+"' ");
				strSQL2.append("AND t5.localFile='"+q_fileName+"' ");
				strSQL3.append("AND t5.localFile='"+q_fileName+"' ");
			}
			if (!"".equalsIgnoreCase(q_performerId)) {
				strSQL1.append( "AND t1.creator='"+q_performerId+"' ");
				strSQL2.append("AND t1.creator='"+q_performerId+"' ");
				strSQL3.append("AND t1.creator='"+q_performerId+"' ");
			}
			if (!"".equalsIgnoreCase(q_squadid)) {
				strSQL1.append( "AND t3.squadid='"+q_squadid+"' ");
				strSQL2.append("AND t3.squadid='"+q_squadid+"' ");
				strSQL3.append("AND t3.squadid='"+q_squadid+"' ");
			}
			
			if ( conn.getMetaData().getDatabaseProductName().contains( "MySQL" ) ) {
				if (!"".equalsIgnoreCase(q_fromDate) && !"".equalsIgnoreCase(q_toDate)) {
					strSQL1.append( "AND t1.createdDateTime BETWEEN '"+q_fromDate+"' AND '"+ q_toDate + "' ");
					strSQL2.append("AND t1.createdDateTime BETWEEN '"+q_fromDate+"' AND '"+ q_toDate + "' ");
					strSQL3.append("AND t1.createdDateTime BETWEEN '"+q_fromDate+"' AND '"+ q_toDate + "' ");
				} else if (!"".equalsIgnoreCase(q_fromDate) && "".equalsIgnoreCase(q_toDate)) {
					strSQL1.append( "AND t1.createdDateTime BETWEEN '"+q_fromDate+"' AND '9999-12-31' ");
					strSQL2.append("AND t1.createdDateTime BETWEEN '"+q_fromDate+"' AND '9999-12-31' ");
					strSQL3.append("AND t1.createdDateTime BETWEEN '"+q_fromDate+"' AND '9999-12-31' ");
				} else if ("".equalsIgnoreCase(q_fromDate) && !"".equalsIgnoreCase(q_toDate)) {
					strSQL1.append( "AND t1.createdDateTime BETWEEN '2000-01-01' AND '"+ q_toDate + "' ");
					strSQL2.append("AND t1.createdDateTime BETWEEN '2000-01-01' AND '"+ q_toDate + "' ");
					strSQL3.append("AND t1.createdDateTime BETWEEN '2000-01-01' AND '"+ q_toDate + "' ");
				}	
			} else if ( conn.getMetaData().getDatabaseProductName().contains( "Informix" ) ) {
				if (!"".equalsIgnoreCase(q_fromDate) && !"".equalsIgnoreCase(q_toDate)) {
					strSQL1.append("AND t1.createdDateTime BETWEEN '"+q_fromDate+" 00:00:00' AND '"+ q_toDate + " 23:59:59' ");
					strSQL2.append("AND t1.createdDateTime BETWEEN '"+q_fromDate.toString()+" 00:00:00' AND '"+ q_toDate.toString() + " 23:59:59' ");
					strSQL3.append("AND t1.createdDateTime BETWEEN '"+q_fromDate+" 00:00:00' AND '"+ q_toDate + " 23:59:59' ");
				} else if (!"".equalsIgnoreCase(q_fromDate) && "".equalsIgnoreCase(q_toDate)) {
					strSQL1.append("AND t1.createdDateTime BETWEEN '"+q_fromDate+" 00:00:00' AND '9999-12-31 23:59:59' ");
					strSQL2.append("AND t1.createdDateTime BETWEEN '"+q_fromDate+" 00:00:00' AND '9999-12-31 23:59:59' ");
					strSQL3.append("AND t1.createdDateTime BETWEEN '"+q_fromDate+" 00:00:00' AND '9999-12-31 23:59:59' ");
				} else if ("".equalsIgnoreCase(q_fromDate) && !"".equalsIgnoreCase(q_toDate)) {
					strSQL1.append( "AND t1.createdDateTime BETWEEN '2000-01-01 00:00:00' AND '"+ q_toDate + " 23:59:59' ");
					strSQL2.append("AND t1.createdDateTime BETWEEN '2000-01-01 00:00:00' AND '"+ q_toDate + " 23:59:59' ");
					strSQL3.append("AND t1.createdDateTime BETWEEN '2000-01-01 00:00:00' AND '"+ q_toDate + " 23:59:59' ");
				}
			}
			
			/** strSQL1不加ORDER 因為它是count */
			strSQL2.append("ORDER BY t2.depmainSN DESC,t2.depdetailid ASC");
			strSQL3.append("GROUP BY t2.depmainSN ORDER BY t2.depmainSN DESC");
		
			strSQL1.replace(0, strSQL1.length(), wid.UI2DB(strSQL1.toString(),conn.getMetaData().getDatabaseProductName() ));
			strSQL2.replace(0, strSQL2.length(), wid.UI2DB(strSQL2.toString(),conn.getMetaData().getDatabaseProductName() ));
			strSQL3.replace(0, strSQL3.length(), wid.UI2DB(strSQL3.toString(),conn.getMetaData().getDatabaseProductName() ));
			
			errPars = wid.toJend("SQL",strSQL2.toString());
			errPars = wid.toJend("SQL",strSQL1.toString());
			errPars = wid.toJend("SQL",strSQL3.toString());
			
			errPars = wid.toJend("strSQL1", strSQL1.toString() );
			
			if ( wid.isDebug ) {
				wid.bugTrace(conn, ps, "strSQL1 tailer", strSQL1.toString().substring(strSQL1.toString().length()/2, strSQL1.toString().length() ) );
				wid.bugTrace(conn, ps, "strSQL1 Header", strSQL1.toString().substring(0, strSQL1.toString().length()/2) );
			}
			
			ps = conn.prepareStatement( strSQL1.toString() );
			rs = ps.executeQuery();
			rs.next();
			
			if ( rs.getInt(1) != 0) {
				ps = conn.prepareStatement( strSQL2.toString() );
				rs = ps.executeQuery();
				ps = conn.prepareStatement(strSQL3.toString() );
				rs2 = ps.executeQuery();
				rs2.next();
				int i = 0,count=1;
				
				while ( rs.next() ) {
					QueryDepRes qdr = new QueryDepRes();
					
					if ( rs.getInt("depmainSN") != rs2.getInt("depmainSN") ) { rs2.next(); }
					qdr.setNonWeb( "t".equalsIgnoreCase( rs.getString("isnonweb")  ) );
					qdr.setEnvId( rs.getString("envid") );
					qdr.setSizeNum( rs2.getInt(2) );
					qdr.setDepmainSN( rs.getInt("depmainSN") );
					qdr.setBssid( rs.getString("bssid") );
					qdr.setCreatedDateTime(wid.changeTimeFormatUI( rs.getTimestamp("createdDateTime")) );
					qdr.setDeliverySN( rs.getInt("deliverySN") );
					qdr.setDepdetailid( rs.getInt("depdetailid") );
					qdr.setDeplmainSN( rs.getInt("deplmainSN") );
					qdr.setSquadid( rs.getString("squadid") );
					qdr.setSubsysid( rs.getString("subsysid") );
					qdr.setUsername( wid.DB2UI( rs.getString("username"),conn.getMetaData().getDatabaseProductName() ) );
					qdr.setEnvName( wid.DB2UI( rs.getString("envname"),conn.getMetaData().getDatabaseProductName() ) );
					qdr.setSquadName( wid.DB2UI( rs.getString("squadname"),conn.getMetaData().getDatabaseProductName() ) );
					qdr.setSubsysName( wid.DB2UI( rs.getString("subsysname"),conn.getMetaData().getDatabaseProductName() ) );
					qdr.setLocalPath( rs.getString("localPath") );
					qdr.setLocalFile( rs.getString("localFile") );
					qdr.setLocalVer( rs.getInt("localVer") );
					qdr.setDeplComment( rs.getString("deplComment") );
					qdr.setDepPath( rs.getString("depPath") );
					
					
					/**	計算資源群組的數目，後續的畫面才能進一步的進行rowspan	*/ 
					if ( i!=0 ) {
						if (qdres.get((i-1)).getDepmainSN() == qdr.getDepmainSN() && qdres.get((i-1)).getDepdetailid()== qdr.getDepdetailid() ){
							count++;
							for (int j=0;j<count-1;j++){
								qdres.get((i-j-1)).setSquadNum(count);
							}
						} else {
							count=1;
						}
						qdr.setSquadNum(count);
					} else {
						qdr.setSquadNum(1);
					}
					qdr.setSquadSN(count);
					
					/**	若是由交付產生的檔案，需進一步地找來源端的代號 */
					if ( qdr.getDeliverySN() != 0 ) {
						qdr.setDeliveryTarSN(rs.getInt("deliveryTarSN"));
						
						
						/* 取得交付端		版本號 */
						strSQL4.replace(0, strSQL4.length(), "SELECT fromVer FROM delsvn WHERE deliverySN=? AND toPath=? AND toFile=? AND toVer=?");
						ps = conn.prepareStatement( strSQL4.toString() );	
						ps.setInt(1, qdr.getDeliverySN());
						ps.setString(2, qdr.getLocalPath());
						ps.setString(3, qdr.getLocalFile());
						ps.setLong(4, qdr.getLocalVer());
						rs3 = ps.executeQuery();
						
						/* 由於這個欄位原先沒有，所以許多歷史記錄會查不到，故加此判斷 */
						if (rs3.next() ) {
							qdr.setFromVer(rs3.getInt("fromVer"));	
						} else {
							qdr.setFromVer(0);
						}
						

						/* 取得交付端		部署計畫號 */
						strSQL4.replace(0, strSQL4.length(), "SELECT t1.tarDeplmainSN,t2.deliveryTarSN FROM deplmain t1,deltask t2 WHERE t1.deplmainSN=t2.deplmainSN AND t1.deplmainSN=?");
						ps = conn.prepareStatement( strSQL4.toString() );
						ps.setInt(1, qdr.getDeplmainSN());
						rs3 = ps.executeQuery();
						
						/* 由於這個欄位原先沒有，所以許多歷史記錄會查不到，故加此判斷 */
						if (rs3.next() ) {
							qdr.setDeplmainTarSN(rs3.getInt("tarDeplmainSN"));
							qdr.setDeliveryTarSN(rs3.getInt("deliveryTarSN"));	
						} else {
							qdr.setDeplmainTarSN(0);
							qdr.setDeliveryTarSN(0);
						}
					}
					qdres.add(qdr);
					i++;
				}
			}
		} catch ( SQLException e) {
			wid.kem(conn, ps, e, wid.toEType("SQLException")+errPars, wid.isPrint2Console );
		} catch ( UnsupportedEncodingException e) {
			wid.kem(conn, ps, e, wid.toEType("UnsupportedEncodingException")+errPars, wid.isPrint2Console );
		} catch ( Exception e) {
			wid.kem(conn, ps, e, wid.toEType("Exception")+errPars, wid.isPrint2Console );
		} finally {
			try {
				if ( rs != null ) { rs.close(); }
				if ( rs2 != null ) { rs2.close(); }
				if ( rs3 != null ) { rs3.close(); }
				if ( ps!= null ) { ps.close(); }
				if ( conn!= null ) { conn.close(); }
			} catch (SQLException e) {
				System.out.println(wid.errProcess(e) );
			}
		}
		
		response.getWriter().write(new Gson().toJson(qdres) );	
	}
}